<?php

namespace App\Http\Controllers\Admin;
header('Content-Type:text/html;charset=utf-8');


use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Http\Requests;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Input;
use Route,URL,Auth,Excel;

class TjController extends Controller
{
	  public function xzzf()
    {
        //判断权限 省级 市级 区级别
    	//1省2市3区4派出所


      	$dengji =  Auth::guard('admin')->user()->dengji;

        return view('admin.tj.xzzf',['dengji'=>$dengji]);
    }

    public function zabw(){
    	$dengji =  Auth::guard('admin')->user()->dengji;
    	$uid = Auth::guard('admin')->user()->id;
    	return view('admin.tj.zabw',['dengji'=>$dengji,'uid'=>$uid]);
    }


    public function findzabw(request $request){
    		//查看登录人信息

    		$user = Auth::guard('admin')->user()->area_id;
    		$dengji = Auth::guard('admin')->user()->dengji;
			$id = $request->id;
            $nid['id'] = $request->id;

            $area = DB::table('area')->where($nid)->first();



    	// if ($user == '' && $dengji == 1) {


    		//治安保卫重点单位统计表（总数）省级的
			if ($id){
                    //省选区
                    if ($area->dengji == 1) {
    			     $city = DB::select("SELECT units.type,area.name,unit_type.name as typename,COUNT(*) as count FROM area LEFT JOIN units on units.area = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE  units.is_imp != 0 AND area.cid =" . $id . " GROUP BY units.type,area.name,unit_type.name");
                    }

                    if ($area->dengji == 2) {
                     $city = DB::select("SELECT units.type,area.name,unit_type.name as typename,COUNT(*) as count FROM area LEFT JOIN units on units.org_id = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE units.is_imp != 0 AND  area.cid =" . $id . " GROUP BY units.type,area.name,unit_type.name");
                    }

                       if ($area->dengji == 3) {
                     $city = DB::select("SELECT units.type,area.name,unit_type.name as typename,COUNT(*) as count FROM area LEFT JOIN units on units.org_id = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE   units.is_imp != 0 AND units.org_id=" . $id . " GROUP BY units.type,area.name,unit_type.name");
                    }
				} else {

                    if ($dengji == 1) {

                        $city = DB::select("SELECT units.type,area.name,unit_type.name as typename,COUNT(*) as count FROM area LEFT JOIN units on units.city = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE (area.cid = 0 and units.is_imp != 0) GROUP BY units.type,area.name,unit_type.name");
                    }

                    if ($dengji == 2) {
                       $city = DB::select("SELECT units.type,area.name,unit_type.name as typename,COUNT(*) as count FROM area LEFT JOIN units on units.area = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE (area.id IN ($user) and units.is_imp != 0) GROUP BY units.type,area.name,unit_type.name");
                    }

                    if ($dengji == 3) {
                       $city = DB::select("SELECT units.type,area.name,unit_type.name as typename,COUNT(*) as count FROM area LEFT JOIN units on units.org_id = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE (area.id IN ($user) and units.is_imp != 0) GROUP BY units.type,area.name,unit_type.name");
                    }

                       if ($dengji == 4) {
                       $city = DB::select("SELECT units.type,area.name,unit_type.name as typename,COUNT(*) as count FROM area LEFT JOIN units on units.org_id = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE (area.id IN ($user) and units.is_imp != 0) GROUP BY units.type,area.name,unit_type.name");
                    }





				
				}

    		$provice = DB::table('area')->where('cid',0)->orderBy('paixu','asc')->select('name')->get();

    		$pr=array();
    		foreach ($provice as $key => $value) {
    			array_push($pr,$value->name);
    		}

    		$unittype = DB::table('unit_type')->select('name')->get();
    		$ut = array();
    		foreach ($unittype as $key => $value) {
    			array_push($ut,$value->name);
    		}

    		// //治安保卫重点单位统计表（合计）
    		// $hcity = DB::select("SELECT units.city ,COUNT(*) as count FROM area LEFT JOIN units on units.city = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE (area.cid = 0 and units.is_imp != 0) GROUP BY units.city");
    		// var_dump($hcity);


	    	$json = array('city'=>$city,'provice'=>$pr,'unittype'=>$ut);
			$jcity = json_encode($json,true);
			echo $jcity;
			exit;
		// }

		// if ($dengji == 2) {

		// 	//权限管理
		// 	echo $user;


		// 	$twoarea = DB::select("SELECT id,name FROM area WHERE id IN($user) and cid != 0 and dengji = 2");

		// 	foreach ($twoarea as $key => $value) {
		// 		$ncid = $value->id;
		// 	}

		// 	//获取城市级别的
		// 	$towcity = DB::select("SELECT units.type,area.name,unit_type.name as typename,COUNT(*) as count FROM area LEFT JOIN units on units.area = area.id LEFT JOIN unit_type on units.type = unit_type.id WHERE (area.id IN ($user) and units.is_imp != 0 and area.cid != 0 and dengji = 2) GROUP BY units.type,area.name,unit_type.name");

		// 	$twoprovice = DB::select("SELECT name FROM area WHERE id IN($user) and cid != 0 and dengji = 2");

  //   		$twopr=array();
  //   		foreach ($twoprovice as $key => $value) {
  //   			array_push($twopr,$value->name);
  //   		}

  //   		$unittype = DB::table('unit_type')->select('name')->get();
  //   		$ut = array();
  //   		foreach ($unittype as $key => $value) {
  //   			array_push($ut,$value->name);
  //   		}

		// 	$json = array('towcity'=>$towcity,'twoprovice'=>$twopr,'unittype'=>$ut);
		// 	$jcity = json_encode($json,true);
		// 	echo $jcity;
		// 	exit;

		// }



    }


      public function dwdt()
    {
        //判断权限 省级 市级 区级别
    	//1省2市3区4派出所

      	$dengji =  Auth::guard('admin')->user()->dengji;

        return view('admin.tj.dwdt',['dengji'=>$dengji]);
    }


       public function zaaf()
    {
        //判断权限 省级 市级 区级别
    	//1省2市3区4派出所

      	$dengji =  Auth::guard('admin')->user()->dengji;

        return view('admin.tj.zaaf',['dengji'=>$dengji]);
    }


    public function dwdtxq(){

      	$dengji =  Auth::guard('admin')->user()->dengji;

        return view('admin.tj.dwdtxq',['dengji'=>$dengji]);
    }

    public function zabwxq(){
    		$dengji =  Auth::guard('admin')->user()->dengji;

        return view('admin.tj.zabwxq',['dengji'=>$dengji]);
    }

      public function zbxq(){
    		$dengji =  Auth::guard('admin')->user()->dengji;

        return view('admin.tj.zbxq',['dengji'=>$dengji]);
    }


    //治安保卫重点单位安防状况统计表
    public function zddwajax(){
        $af = DB::select("SELECT unit_type.id,unit_type.name as typename, sum(case when zhongyaobuwei.anquanzhidu IS NOT NULL then 1 else 0 end)as 'zd',sum(case when zhongyaobuwei.renfangtiaojian IS NOT NULL then 1 else 0 end)as 'rf',sum(case when zhongyaobuwei.wufangtiaojian IS NOT NULL then 1 else 0 end)as 'wf',sum(case when zhongyaobuwei.jifangtiaojian IS NOT NULL then 1 else 0 end)as 'jf',sum(case when zhongyaobuwei.baoanfuwu IS NOT NULL then 1 else 0 end)as 'ba' FROM zhongyaobuwei LEFT JOIN units on units.id = zhongyaobuwei.unit_id LEFT JOIN unit_type ON units.type = unit_type.id GROUP BY unit_type.id,typename");

        $json = array('af'=>$af);
        $jaf = json_encode($json,true);
        echo $jaf;
        exit;  

    }


    //单位动态治安保卫重点单位
    public function dwdtajax(request $request){

        $user = Auth::guard('admin')->user()->area_id;
        $dengji = Auth::guard('admin')->user()->dengji;
        $id = $request->id;
        $nid['id'] = $request->id;
        $area = DB::table('area')->where($nid)->first();



        if ($user == '') {
            if ($id){

               if ($area->dengji == 1) {


                     $time = DB::select("SELECT MAX(add_time) AS t FROM units_qutotal");
                        date_default_timezone_set('Asia/Shanghai');
                        $newtime= date('Y-m-d H:i:s',strtotime("-1 minute"));
                    foreach ($time as $key => $value) {
                            $tt = $value->t;
                        }

                        if ($time) {
                       
                           
                                DB::table('units_qutotal')->delete();
                                DB::insert(" INSERT INTO units_qutotal(area_name, zdbw) SELECT area.name , COUNT(1) AS zybw FROM units JOIN area ON area.id = units.area LEFT JOIN zhongyaobuwei ON units.id  = zhongyaobuwei.unit_id WHERE area.cid =  ". $id ." GROUP BY area.name");
                                DB::update("UPDATE units_qutotal SET units_qutotal.bwry = (SELECT COUNT(1) AS bwry FROM units JOIN area ON area.id  = units.area LEFT JOIN baoweirenyuan ON units.id = baoweirenyuan.unit_id WHERE area.cid = ". $id ." AND area.name = units_qutotal.area_name  GROUP BY area.name)");
                                DB::update("UPDATE units_qutotal SET units_qutotal.zdry = (SELECT COUNT(1) AS zdry FROM units JOIN area ON area.id = units.area LEFT JOIN zhongdianrenyuan ON units.id = zhongdianrenyuan.unit_id WHERE  area.cid = ". $id ."  AND area.name = units_qutotal.area_name GROUP BY area.name)");

                                //查询表中数据
                                $hq = DB::select("SELECT area_name,zdbw,bwry,zdry FROM  units_qutotal");
                                $json = array('hq'=>$hq);
                                $jaf = json_encode($json,true);
                                echo $jaf;
                                exit;  
                            
                           
                            }else{

                                DB::table('units_qutotal')->delete();
                                DB::insert(" INSERT INTO units_qutotal(area_name, zdbw) SELECT area.name , COUNT(1) AS zybw FROM units JOIN area ON area.id = units.area LEFT JOIN zhongyaobuwei ON units.id  = zhongyaobuwei.unit_id WHERE area.cid =  ". $id ." GROUP BY area.name");
                                DB::update("UPDATE units_qutotal SET units_qutotal.bwry = (SELECT COUNT(1) AS bwry FROM units JOIN area ON area.id  = units.area LEFT JOIN baoweirenyuan ON units.id = baoweirenyuan.unit_id WHERE area.cid = ". $id ." AND area.name = units_qutotal.area_name  GROUP BY area.name)");
                                DB::update("UPDATE units_qutotal SET units_qutotal.zdry = (SELECT COUNT(1) AS zdry FROM units JOIN area ON area.id = units.area LEFT JOIN zhongdianrenyuan ON units.id = zhongdianrenyuan.unit_id WHERE  area.cid = ". $id ."  AND area.name = units_qutotal.area_name GROUP BY area.name)");

                                //查询表中数据
                                $hq = DB::select("SELECT area_name,zdbw,bwry,zdry FROM  units_qutotal");
                                $json = array('hq'=>$hq);
                                $jaf = json_encode($json,true);
                                echo $jaf;
                                exit;   
                            }

                   

                }
                if ($area->dengji == 2) {

                   $time = DB::select("SELECT MAX(add_time) AS t FROM units_areatotal");
                    date_default_timezone_set('Asia/Shanghai');
                    $newtime= date('Y-m-d H:i:s',strtotime("-1 minute"));
                    foreach ($time as $key => $value) {
                        $tt = $value->t;
                    }

           

                if ($time) {
                   

                        DB::table('units_areatotal')->delete();
                        DB::insert(" INSERT INTO units_areatotal(area_name, zdbw) SELECT area.name , COUNT(1) AS zybw FROM units JOIN area ON area.id = units.org_id LEFT JOIN zhongyaobuwei ON units.id  = zhongyaobuwei.unit_id WHERE area.cid =  ". $id ." GROUP BY area.name");
                        DB::update("UPDATE units_areatotal SET units_areatotal.bwry = (SELECT COUNT(1) AS bwry FROM units JOIN area ON area.id  = units.org_id LEFT JOIN baoweirenyuan ON units.id = baoweirenyuan.unit_id WHERE area.cid = ". $id ." AND area.name = units_areatotal.area_name  GROUP BY area.name)");
                        DB::update("UPDATE units_areatotal SET units_areatotal.zdry = (SELECT COUNT(1) AS zdry FROM units JOIN area ON area.id = units.org_id LEFT JOIN zhongdianrenyuan ON units.id = zhongdianrenyuan.unit_id WHERE  area.cid = ". $id ."  AND area.name = units_areatotal.area_name GROUP BY area.name)");

                        //查询表中数据
                        $hq = DB::select("SELECT area_name,zdbw,bwry,zdry FROM  units_areatotal");
                        $json = array('hq'=>$hq);
                        $jaf = json_encode($json,true);
                        echo $jaf;
                        exit;  

                }else{

                        DB::table('units_areatotal')->delete();
                        DB::insert(" INSERT INTO units_areatotal(area_name, zdbw) SELECT area.name , COUNT(1) AS zybw FROM units JOIN area ON area.id = units.org_id LEFT JOIN zhongyaobuwei ON units.id  = zhongyaobuwei.unit_id WHERE area.cid =  ". $id ." GROUP BY area.name");
                        DB::update("UPDATE units_areatotal SET units_areatotal.bwry = (SELECT COUNT(1) AS bwry FROM units JOIN area ON area.id  = units.org_id LEFT JOIN baoweirenyuan ON units.id = baoweirenyuan.unit_id WHERE area.cid = ". $id ." AND area.name = units_areatotal.area_name  GROUP BY area.name)");
                        DB::update("UPDATE units_areatotal SET units_areatotal.zdry = (SELECT COUNT(1) AS zdry FROM units JOIN area ON area.id = units.org_id LEFT JOIN zhongdianrenyuan ON units.id = zhongdianrenyuan.unit_id WHERE  area.cid = ". $id ."  AND area.name = units_areatotal.area_name GROUP BY area.name)");

                        //查询表中数据
                        $hq = DB::select("SELECT area_name,zdbw,bwry,zdry FROM  units_areatotal");
                        $json = array('hq'=>$hq);
                        $jaf = json_encode($json,true);
                        echo $jaf;
                        exit;  


                }

              }

            


            }else{

                $time = DB::select("SELECT MAX(add_time) AS t FROM units_total");
                date_default_timezone_set('Asia/Shanghai');
                $newtime= date('Y-m-d H:i:s',strtotime("-1 minute"));
                foreach ($time as $key => $value) {
                    $tt = $value->t;
                }

               
                //小于当前减1分钟的时间

                  if ($time) {
                    if ($newtime>$tt) {
                            DB::table('units_total')->delete();
                            DB::insert(" INSERT INTO units_total(area_name, zdbw) SELECT area.name , COUNT(1) AS zybw FROM units JOIN area ON area.id = units.city LEFT JOIN zhongyaobuwei ON units.id  = zhongyaobuwei.unit_id WHERE area.cid = 0 GROUP BY area.name");
                            DB::update("UPDATE units_total SET units_total.bwry = (SELECT COUNT(1) AS bwry FROM units JOIN area ON area.id  = units.city LEFT JOIN baoweirenyuan ON units.id = baoweirenyuan.unit_id WHERE area.cid = 0 AND area.name = units_total.area_name  GROUP BY area.name)");
                            DB::update("UPDATE units_total SET units_total.zdry = (SELECT COUNT(1) AS zdry FROM units JOIN area ON area.id = units.city LEFT JOIN zhongdianrenyuan ON units.id = zhongdianrenyuan.unit_id WHERE  area.cid = 0  AND area.name = units_total.area_name GROUP BY area.name)");

                            //查询表中数据
                            $hq = DB::select("SELECT area_name,zdbw,bwry,zdry FROM  units_total");
                            $json = array('hq'=>$hq);
                            $jaf = json_encode($json,true);
                            echo $jaf;
                            exit;  
                        }else{

                            //查询表中数据
                            $hq = DB::select("SELECT area_name,zdbw,bwry,zdry FROM  units_total");
                            $json = array('hq'=>$hq);
                            $jaf = json_encode($json,true);
                            echo $jaf;
                            exit;  
                        }

                    }else{
                        DB::table('units_total')->delete();
                        DB::insert(" INSERT INTO units_total(area_name, zdbw) SELECT area.name , COUNT(1) AS zybw FROM units JOIN area ON area.id = units.city LEFT JOIN zhongyaobuwei ON units.id  = zhongyaobuwei.unit_id WHERE area.cid = 0 GROUP BY area.name");
                        DB::update("UPDATE units_total SET units_total.bwry = (SELECT COUNT(1) AS bwry FROM units JOIN area ON area.id  = units.city LEFT JOIN baoweirenyuan ON units.id = baoweirenyuan.unit_id WHERE area.cid = 0 AND area.name = units_total.area_name  GROUP BY area.name)");
                        DB::update("UPDATE units_total SET units_total.zdry = (SELECT COUNT(1) AS zdry FROM units JOIN area ON area.id = units.city LEFT JOIN zhongdianrenyuan ON units.id = zhongdianrenyuan.unit_id WHERE  area.cid = 0  AND area.name = units_total.area_name GROUP BY area.name)");

                        //查询表中数据
                        $hq = DB::select("SELECT area_name,zdbw,bwry,zdry FROM  units_total");
                        $json = array('hq'=>$hq);
                        $jaf = json_encode($json,true);
                        echo $jaf;
                        exit;  

                    }
                }

         
     
        }
    }



    public function dwsl(){
         return view('admin.tj.dwsl');
    }

    public function daodwsl(request $request){

        $dengji = Auth::guard('admin')->user()->dengji;
        $id = $request->id;
        $nid['id'] = $request->id;
        $area = DB::table('area')->where($nid)->first();


        if ($id) {

             if ($area->dengji == 1) {
                $city = DB::select("SELECT area.name,COUNT(*) as value FROM units LEFT JOIN area on area.id = units.area WHERE area.cid = $id GROUP BY area.name");

             }

             if ($area->dengji == 2) {
                $city = DB::select("SELECT area.name,COUNT(*) as value FROM units LEFT JOIN area on area.id = units.org_id WHERE area.cid = $id GROUP BY area.name");
             }

              if ($area->dengji == 3) {
                $city = DB::select("SELECT area.name,COUNT(*) as value FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id = $id GROUP BY area.name");
             }
        }else{


            if ($dengji == 1) {
            //获取全省下的市下的单位数
             $city = DB::select("SELECT area.name,COUNT(*) as value FROM units LEFT JOIN area on area.id = units.city WHERE area.cid = 0 GROUP BY area.name");

            
            }

            if ($dengji == 2) {
                $areaid = Auth::guard('admin')->user()->area_id;
                $city = DB::select(" SELECT area.name,COUNT(*) as value FROM units LEFT JOIN area on area.id = units.area WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name");
                
            }

            if ($dengji == 3) {
                $areaid = Auth::guard('admin')->user()->area_id;
                $city = DB::select(" SELECT area.name,COUNT(*) as value FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 3 GROUP BY area.name");
                
            }

            if ($dengji == 4) {
                $areaid = Auth::guard('admin')->user()->area_id;
                $city = DB::select(" SELECT area.name,COUNT(*) as value FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 3 GROUP BY area.name");
            
            }

        }

         $cellData[] = ['地址','数量'];
         foreach ($city as $key => $v) {
             $cellData[$key+1] = [$v->name,$v->value];
         }

        Excel::create('单位数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
            $excel->sheet('score', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->export('xls');
    }


    public function jbsl(){
         return view('admin.tj.jbsl');
    }


    public function daojbsl(request $request){

        $dengji = Auth::guard('admin')->user()->dengji;
        $id = $request->id;
        $nid['id'] = $request->id;
        $area = DB::table('area')->where($nid)->first();
        if ($id) {
                 if ($area->dengji == 1) {
                    $city = DB::select("SELECT SUM(units.is_imp = 0) as 非重点,SUM(units.is_imp = 1) as 省级,SUM(units.is_imp = 2) as 市级,SUM(units.is_imp = 3) as 县级, SUM(units.is_imp = 4) as 乡级 FROM units LEFT JOIN area on area.id = units.area WHERE area.cid = $id");
                 }

                 if ($area->dengji == 2) {
                    $city = DB::select("SELECT SUM(units.is_imp = 0) as 非重点,SUM(units.is_imp = 1) as 省级,SUM(units.is_imp = 2) as 市级,SUM(units.is_imp = 3) as 县级, SUM(units.is_imp = 4) as 乡级 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.cid = $id");
                 }

                  if ($area->dengji == 3) {
                    $city = DB::select("SELECT SUM(units.is_imp = 0) as 非重点,SUM(units.is_imp = 1) as 省级,SUM(units.is_imp = 2) as 市级,SUM(units.is_imp = 3) as 县级, SUM(units.is_imp = 4) as 乡级 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id = $id");
                 }

        }else{

                if ($dengji == 1) {
                //获取全省下的级别统计数量
                 $city = DB::select("SELECT SUM(is_imp = 0) as 非重点,SUM(is_imp = 1) as 省级,SUM(is_imp = 2) as 市级,SUM(is_imp = 3) as 县级, SUM(is_imp = 4) as 乡级 FROM units");
                }


                if ($dengji == 2) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.is_imp = 0) as 非重点,SUM(units.is_imp = 1) as 省级,SUM(units.is_imp = 2) as 市级,SUM(units.is_imp = 3) as 县级, SUM(units.is_imp = 4) as 乡级 FROM units LEFT JOIN area on area.id = units.area WHERE area.id IN ($areaid) and area.dengji = 2");
                }

                if ($dengji == 3) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.is_imp = 0) as 非重点,SUM(units.is_imp = 1) as 省级,SUM(units.is_imp = 2) as 市级,SUM(units.is_imp = 3) as 县级, SUM(units.is_imp = 4) as 乡级 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 3");
                    
                }

                if ($dengji == 4) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.is_imp = 0) as 非重点,SUM(units.is_imp = 1) as 省级,SUM(units.is_imp = 2) as 市级,SUM(units.is_imp = 3) as 县级, SUM(units.is_imp = 4) as 乡级 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 3");
                
                }

            }

        
         $cellData[] = ['非重点','省级','市级','县级','乡级'];
         foreach ($city as $key => $v) {
             $cellData[$key+1] = [$v->非重点,$v->省级,$v->市级,$v->县级,$v->乡级];
         }

        Excel::create('单位级别数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
            $excel->sheet('score', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->export('xls');
    }

       public function basl(){
         return view('admin.tj.basl');
    }

    public function daobasl(request $request){
        $dengji = Auth::guard('admin')->user()->dengji;
        $id = $request->id;
        $nid['id'] = $request->id;
        $area = DB::table('area')->where($nid)->first();


        if ($id) {
                 if ($area->dengji == 1) {
                    $city = DB::select("SELECT area.name,COUNT(*) as value FROM baoan_company LEFT JOIN area on area.id = baoan_company.area WHERE area.cid = $id GROUP BY area.name");
                 }

                 if ($area->dengji == 2) {
                        $city = DB::select("SELECT area.name,COUNT(*) as value FROM baoan_company LEFT JOIN area on area.id = baoan_company.org_id WHERE area.cid = $id GROUP BY area.name");
                 }

                 if ($area->dengji == 3) {
                    $city = DB::select("SELECT area.name,COUNT(*) as value FROM baoan_company LEFT JOIN area on area.id = baoan_company.org_id WHERE area.id = $id GROUP BY area.name");
                 }

            }else{
                if ($dengji == 1) {
                //获取全省下的市下的保安公司
                 $city = DB::select("SELECT area.name,COUNT(*) as value FROM baoan_company LEFT JOIN area on area.id = baoan_company.city WHERE area.cid = 0 GROUP BY area.name");

                
                }

                if ($dengji == 2) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,COUNT(*) as value FROM baoan_company LEFT JOIN area on area.id = baoan_company.area WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name");
                    
                }

                if ($dengji == 3) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,COUNT(*) as value FROM baoan_company LEFT JOIN area on area.id = baoan_company.org_id WHERE area.id IN ($areaid) and area.dengji = 3 GROUP BY area.name");
                    
                }

                if ($dengji == 4) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,COUNT(*) as value FROM baoan_company LEFT JOIN area on area.id = baoan_company.org_id WHERE area.id IN ($areaid) and area.dengji = 3 GROUP BY area.name");
                
                }
            }

              $cellData[] = ['地区','单位数量'];
             foreach ($city as $key => $v) {
                 $cellData[$key+1] = [$v->name,$v->value];
             }

            Excel::create('保安公司数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
                $excel->sheet('score', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');





    }


    public function bwry(){
         return view('admin.tj.bwry');
    }


     public function daobwry(request $request){
        $dengji = Auth::guard('admin')->user()->dengji;
        $id = $request->id;
        $nid['id'] = $request->id;
        $area = DB::table('area')->where($nid)->first();

        if ($id) {
                 if ($area->dengji == 1) {
                     $city = DB::select("SELECT area.name,count(1)as value FROM baoweirenyuan LEFT JOIN units ON units.id = baoweirenyuan.unit_id LEFT JOIN area ON area.id = units.area WHERE area.cid = $id GROUP BY area.name");
                 }

                 if ($area->dengji == 2) {
                    $city = DB::select("SELECT area.name,count(1)as value FROM baoweirenyuan LEFT JOIN units ON units.id = baoweirenyuan.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.cid = $id GROUP BY area.name");
                 }

                  if ($area->dengji == 3) {
                     $city = DB::select("SELECT area.name,count(1)as value FROM baoweirenyuan LEFT JOIN units ON units.id = baoweirenyuan.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id = $id GROUP BY area.name");
                 }

            }else{

                if ($dengji == 1) {
                //获取全省下的级别统计数量
                 $city = DB::select("SELECT area.name,count(1)as value FROM baoweirenyuan LEFT JOIN units ON units.id = baoweirenyuan.unit_id LEFT JOIN area ON area.id = units.city WHERE area.cid = 0 and area.dengji = 1 GROUP BY area.name");
                }


                if ($dengji == 2) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM baoweirenyuan LEFT JOIN units ON units.id = baoweirenyuan.unit_id LEFT JOIN area ON area.id = units.area WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name");
                }

                if ($dengji == 3) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM baoweirenyuan LEFT JOIN units ON units.id = baoweirenyuan.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name ");
                    
                }

                if ($dengji == 4) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM baoweirenyuan LEFT JOIN units ON units.id = baoweirenyuan.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name ");
                
                }

            }



               $cellData[] = ['地区','保卫人员数量'];
             foreach ($city as $key => $v) {
                 $cellData[$key+1] = [$v->name,$v->value];
             }

            Excel::create('保卫人员数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
                $excel->sheet('score', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');








     }



    public function zdbw(){
        return view('admin.tj.zdbw');
    }


    public function daozdbw(request $request){
       $dengji = Auth::guard('admin')->user()->dengji;
         $id = $request->id;
        $nid['id'] = $request->id;
        $area = DB::table('area')->where($nid)->first();


        if ($id) {
                 if ($area->dengji == 1) {
                     $city = DB::select("SELECT area.name,count(1)as value FROM zhongyaobuwei LEFT JOIN units ON units.id = zhongyaobuwei.unit_id LEFT JOIN area ON area.id = units.area WHERE area.cid = $id GROUP BY area.name");
                 }

                 if ($area->dengji == 2) {
                    $city = DB::select("SELECT area.name,count(1)as value FROM zhongyaobuwei LEFT JOIN units ON units.id = zhongyaobuwei.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.cid = $id GROUP BY area.name");
                 }

                  if ($area->dengji == 3) {
                    $city = DB::select("SELECT area.name,count(1)as value FROM zhongyaobuwei LEFT JOIN units ON units.id = zhongyaobuwei.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id = $id GROUP BY area.name");
                 }

            }else{
                if ($dengji == 1) {
                //获取全省下的级别统计数量
                 $city = DB::select("SELECT area.name,count(1)as value FROM zhongyaobuwei LEFT JOIN units ON units.id = zhongyaobuwei.unit_id LEFT JOIN area ON area.id = units.city WHERE area.cid = 0 and area.dengji = 1 GROUP BY area.name");
                }


                if ($dengji == 2) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM zhongyaobuwei LEFT JOIN units ON units.id = zhongyaobuwei.unit_id LEFT JOIN area ON area.id = units.area WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name");
                }

                if ($dengji == 3) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM zhongyaobuwei LEFT JOIN units ON units.id = zhongyaobuwei.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name ");
                    
                }

                if ($dengji == 4) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM zhongyaobuwei LEFT JOIN units ON units.id = zhongyaobuwei.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name ");
                
                }
            }


             $cellData[] = ['地区','重点部位数量'];
             foreach ($city as $key => $v) {
                 $cellData[$key+1] = [$v->name,$v->value];
             }

            Excel::create('重点部位数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
                $excel->sheet('score', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');





    }

    public function zdgw(){
        return view('admin.tj.zdgw');
    }



    public function daozdgw(request $request){
        $dengji = Auth::guard('admin')->user()->dengji;
        $id = $request->id;
        $nid['id'] = $request->id;
        $area = DB::table('area')->where($nid)->first();

        if ($id) {
                 if ($area->dengji == 1) {
                    $city = DB::select("SELECT area.name,count(1)as value FROM zhongdianrenyuan LEFT JOIN units ON units.id = zhongdianrenyuan.unit_id LEFT JOIN area ON area.id = units.area WHERE area.cid = $id GROUP BY area.name");
                 }

                 if ($area->dengji == 2) {
                        $city = DB::select("SELECT area.name,count(1)as value FROM zhongdianrenyuan LEFT JOIN units ON units.id = zhongdianrenyuan.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.cid = $id GROUP BY area.name");
                 }

                 if ($area->dengji == 3) {
                        $city = DB::select("SELECT area.name,count(1)as value FROM zhongdianrenyuan LEFT JOIN units ON units.id = zhongdianrenyuan.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id = $id GROUP BY area.name");
                 }

            }else{
                if ($dengji == 1) {
                //获取全省下的级别统计数量
                 $city = DB::select("SELECT area.name,count(1)as value FROM zhongdianrenyuan LEFT JOIN units ON units.id = zhongdianrenyuan.unit_id LEFT JOIN area ON area.id = units.city WHERE area.cid = 0 and area.dengji = 1 GROUP BY area.name");
                }


                if ($dengji == 2) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM zhongdianrenyuan LEFT JOIN units ON units.id = zhongdianrenyuan.unit_id LEFT JOIN area ON area.id = units.area WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name");
                }

                if ($dengji == 3) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM zhongdianrenyuan LEFT JOIN units ON units.id = zhongdianrenyuan.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name ");
                    
                }

                if ($dengji == 4) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT area.name,count(1)as value FROM zhongdianrenyuan LEFT JOIN units ON units.id = zhongdianrenyuan.unit_id LEFT JOIN area ON area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 2 GROUP BY area.name ");
                
                }
            }



             $cellData[] = ['地区','重点岗位人员数量'];
             foreach ($city as $key => $v) {
                 $cellData[$key+1] = [$v->name,$v->value];
             }

            Excel::create('重点岗位人员数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
                $excel->sheet('score', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');

    }



    public function dwzt(){
        return view('admin.tj.dwzt');
    }

    public function daodwzt(request $request){
            $dengji = Auth::guard('admin')->user()->dengji;
            $id = $request->id;
            $nid['id'] = $request->id;
            $area = DB::table('area')->where($nid)->first();


                if ($id) {
                 if ($area->dengji == 1) {
                    $city = DB::select("SELECT SUM(units.status = 1) as 实有,SUM(units.status = 2) as 变更,SUM(units.status = 3) as 注销 FROM units LEFT JOIN area on area.id = units.area WHERE area.cid = $id");
                 }

                 if ($area->dengji == 2) {
                        $city = DB::select("SELECT SUM(units.status = 1) as 实有,SUM(units.status = 2) as 变更,SUM(units.status = 3) as 注销 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.cid = $id");
                 }

                 if ($area->dengji == 3) {
                        $city = DB::select("SELECT SUM(units.status = 1) as 实有,SUM(units.status = 2) as 变更,SUM(units.status = 3) as 注销 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id = $id");
                 }

            }else{

                if ($dengji == 1) {
                //获取全省下的级别统计数量
                 $city = DB::select("SELECT SUM(units.status = 1) as 实有,SUM(units.status = 2) as 变更,SUM(units.status = 3) as 注销 FROM units");
                }


                if ($dengji == 2) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.status = 1) as 实有,SUM(units.status = 2) as 变更,SUM(units.status = 3) as 注销 FROM units LEFT JOIN area on area.id = units.area WHERE area.id IN ($areaid) and area.dengji = 2");
                }

                if ($dengji == 3) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.status = 1) as 实有,SUM(units.status = 2) as 变更,SUM(units.status = 3) as 注销 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 3");
                    
                }

                if ($dengji == 4) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.status = 1) as 实有,SUM(units.status = 2) as 变更,SUM(units.status = 3) as 注销 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 3");
                
                }
            }


            $cellData[] = ['实有','变更','注销'];
            foreach ($city as $key => $v) {
                 $cellData[$key+1] = [$v->实有,$v->变更,$v->注销];
             }

            Excel::create('单位状态数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
                $excel->sheet('score', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');





    }

    public function hylb(){
        $type = db::table('unit_type')->orderby('id','asc')->get();
        return view('admin.tj.hylb',['type'=>$type]);
    }

    public function daohylb(request $request){
        $dengji = Auth::guard('admin')->user()->dengji;
        $id = $request->id;
        $nid['id'] = $request->id;
        $area = DB::table('area')->where($nid)->first();

        if ($id) {
                 if ($area->dengji == 1) {
                    $city = DB::select("SELECT SUM(units.type = 1) as 党政机关,SUM(units.type = 2) as 卫生系统, SUM(units.type = 3) as 高校,SUM(units.type = 4) as 中小学幼儿园, SUM(units.type = 5) as 商场超市,SUM(units.type = 6) as 银行金融机构, SUM(units.type = 7) as 加油站,SUM(units.type = 8) as 其他单位 FROM units LEFT JOIN area on area.id = units.area WHERE area.cid = $id");
                 }

                 if ($area->dengji == 2) {
                        $city = DB::select("SELECT SUM(units.type = 1) as 党政机关,SUM(units.type = 2) as 卫生系统, SUM(units.type = 3) as 高校,SUM(units.type = 4) as 中小学幼儿园, SUM(units.type = 5) as 商场超市,SUM(units.type = 6) as 银行金融机构, SUM(units.type = 7) as 加油站,SUM(units.type = 8) as 其他单位 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.cid = $id");
                 }

                 if ($area->dengji == 3) {
                            $city = DB::select("SELECT SUM(units.type = 1) as 党政机关,SUM(units.type = 2) as 卫生系统, SUM(units.type = 3) as 高校,SUM(units.type = 4) as 中小学幼儿园, SUM(units.type = 5) as 商场超市,SUM(units.type = 6) as 银行金融机构, SUM(units.type = 7) as 加油站,SUM(units.type = 8) as 其他单位 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id = $id");
                 }

            }else{
                    if ($dengji == 1) {
                //获取全省下的级别统计数量
                 $city = DB::select("SELECT SUM(units.type = 1) as 党政机关,SUM(units.type = 2) as 卫生系统, SUM(units.type = 3) as 高校,SUM(units.type = 4) as 中小学幼儿园, SUM(units.type = 5) as 商场超市,SUM(units.type = 6) as 银行金融机构, SUM(units.type = 7) as 加油站,SUM(units.type = 8) as 其他单位 FROM units");
                }


                if ($dengji == 2) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.type = 1) as 党政机关,SUM(units.type = 2) as 卫生系统, SUM(units.type = 3) as 高校,SUM(units.type = 4) as 中小学幼儿园, SUM(units.type = 5) as 商场超市,SUM(units.type = 6) as 银行金融机构, SUM(units.type = 7) as 加油站,SUM(units.type = 8) as 其他单位 FROM units LEFT JOIN area on area.id = units.area WHERE area.id IN ($areaid) and area.dengji = 2");
                }

                if ($dengji == 3) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.type = 1) as 党政机关,SUM(units.type = 2) as 卫生系统, SUM(units.type = 3) as 高校,SUM(units.type = 4) as 中小学幼儿园, SUM(units.type = 5) as 商场超市,SUM(units.type = 6) as 银行金融机构, SUM(units.type = 7) as 加油站,SUM(units.type = 8) as 其他单位 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 3");
                    
                }

                if ($dengji == 4) {
                    $areaid = Auth::guard('admin')->user()->area_id;
                    $city = DB::select(" SELECT SUM(units.type = 1) as 党政机关,SUM(units.type = 2) as 卫生系统, SUM(units.type = 3) as 高校,SUM(units.type = 4) as 中小学幼儿园, SUM(units.type = 5) as 商场超市,SUM(units.type = 6) as 银行金融机构, SUM(units.type = 7) as 加油站,SUM(units.type = 8) as 其他单位 FROM units LEFT JOIN area on area.id = units.org_id WHERE area.id IN ($areaid) and area.dengji = 3");
                
                }
            }

            $cellData[] = ['党政机关','卫生系统','高校','中小学幼儿园','商场超市','银行金融机构','其他单位'];
            foreach ($city as $key => $v) {
                 $cellData[$key+1] = [$v->党政机关,$v->卫生系统,$v->高校,$v->中小学幼儿园,$v->商场超市,$v->银行金融机构,$v->其他单位];
             }

            Excel::create('行业类别数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
                $excel->sheet('score', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');





    }

    public function hygk(){
           return view('admin.tj.hygk');
    }

    public function daohygk(request $request){


            $dengji = Auth::guard('admin')->user()->dengji;
            $id = $request->id;
           

            if ($id) {

                $nid['id'] = $request->id;
                $area = DB::table('area')->where($nid)->first();
                $newarea = $area->name;
                
                 if ($area->dengji == 1) {
                        $sy = DB::select("SELECT COUNT(*) as value FROM gasup  WHERE city = '$newarea' ");
                        $kd = DB::select("SELECT COUNT(*) as value FROM kdxt  WHERE city ='$newarea'");
                        $ky = DB::select("SELECT COUNT(*) as value FROM keyun  WHERE city = '$newarea'");
                        $ba = DB::select("SELECT COUNT(*) as value FROM bajg  WHERE city = '$newarea'");

                 }

                 if ($area->dengji == 2) {
                        $sy = DB::select("SELECT COUNT(*) as value FROM gasup  WHERE area = '$newarea' ");
                        $kd = DB::select("SELECT COUNT(*) as value FROM kdxt  WHERE area ='$newarea'");
                        $ky = DB::select("SELECT COUNT(*) as value FROM keyun  WHERE area = '$newarea'");
                        $ba = DB::select("SELECT COUNT(*) as value FROM bajg  WHERE area = '$newarea'");
                 }

                 if ($area->dengji == 3) {
                        $sy = DB::select("SELECT COUNT(*) as value FROM gasup  WHERE org_id = '$newarea' ");
                        $kd = DB::select("SELECT COUNT(*) as value FROM kdxt  WHERE org_id ='$newarea'");
                        $ky = DB::select("SELECT COUNT(*) as value FROM keyun  WHERE org_id = '$newarea'");
                        $ba = DB::select("SELECT COUNT(*) as value FROM bajg  WHERE org_id = '$newarea'");
                 }


            }else{
               
                    $sy = DB::select("SELECT COUNT(*) as value FROM gasup  WHERE province = '湖北省'");
                    $kd = DB::select("SELECT COUNT(*) as value FROM kdxt  WHERE province = '湖北省'");
                    $ky = DB::select("SELECT COUNT(*) as value FROM keyun  WHERE province = '湖北省'");
                    $ba = DB::select("SELECT COUNT(*) as value FROM bajg  WHERE province = '湖北省'");

            
            }

        
            $cellData[] = ['散装汽油系统','快递系统','长途客运系统','保安监管系统'];

            $cellData[1] = [$sy[0]->value,$kd[0]->value,$ky[0]->value,$ba[0]->value];
          

            Excel::create('行业类别数量导出_'.date('YmdHis',time()),function($excel) use ($cellData){
                $excel->sheet('score', function($sheet) use ($cellData){
                    $sheet->rows($cellData);
                });
            })->export('xls');




    }

    









}
